--------------------------------------------------------------------------------
-- @name: Tracing SQL history
-- @author: dion cho
-- @note: test case for tracing SQL execution history of specific session
--------------------------------------------------------------------------------


----------------------------------------------------
-- Case 1: v$session

-- session #1
set echo on
set pages 10000
set lines 200
set timing off
set trimspool on

spool session1.txt

exec dbms_application_info.set_client_info('session1');

exec tpack.wait_for_signal;

declare
	v1		number;
begin
	for idx in 1 .. 1000 loop
		execute immediate 'select /* placeholder */ ' || idx || 
			' from dual where tpack.sleep(1,0.01)=1' into v1;
		dbms_lock.sleep(0.01);
	end loop;
end;
/

spool off


-- session #2

set echo on
set pages 10000
set lines 200
set timing off
set trimspool on

spool session2.txt

-- monitor v$session
col sid new_value sid
select sid from v$session where client_info = 'session1';

exec tpack.signal;

col sql_text format a30
select 
	s.sql_id, s.sql_child_number, t.sql_text
from 
	v$session s,
	v$sql t
where 
	s.sid = &sid
	and (s.sql_id = t.sql_id(+) and s.sql_child_number = t.child_number(+))
;	

/
/
/


-- active profiling
col sid new_value sid
select sid from v$session where client_info = 'session1';

col sql_text format a30
select /*+ ordered use_nl(y) */
	y.sql_id, y.child_no, y.status, y.sql_text,
	count(*) as hit_cnt
from
	(select /*+ no_merge */ 1 from dual connect by level <= 100000) x,
	(select /*+ no_merge */ s.sql_id, s.sql_child_number as child_no, s.status, t.sql_text
	 from v$session s, v$sql t
	 where sid = &sid
		and (s.sql_id = t.sql_id(+) and s.sql_child_number = t.child_number(+))) y
where
	y.sql_text like 'select /* placeholder */%'
group by
	y.sql_id, y.child_no, y.status, y.sql_text
order by 4 desc
;
	

	
-----------------------------------------------------------------
-- Case 2: v$active_session_history

-- session #2
col sid new_value sid
select sid from v$session where client_info = 'session1';

select * from (
	select
		sql_id, sql_child_number,
		(select s.sql_text from v$sql s
			where s.sql_id = h.sql_id and s.child_number = h.sql_child_number) as sql_text
	from v$active_session_history h
	where session_id = &sid
	order by sample_time desc
) where 
	sql_text like 'select /* placeholder */%'
	and rownum <= 20
;


--------------------------------------------------------------------
-- Case 3: sql*trace

-- session #2
col sid new_value sid
select sid from v$session where client_info = 'session1';

exec tpack.begin_diag_trace(&sid, 10046, 1);

exec dbms_lock.sleep(5);

exec tpack.end_diag_trace(&sid, 10046);

select * 
from table(tpack.get_diag_trace(&sid))
where column_value like 'select /* placeholder */%';

	
spool off





